from typing import List, Optional, Dict, Any
from .db import get_db
import sqlite3


# 员工相关操作
def get_all_employees() -> List[Dict[str, Any]]:
    """获取所有员工信息"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM employees")
        columns = [description[0] for description in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]


def get_employee(employee_id: str) -> Optional[Dict[str, Any]]:
    """获取指定员工信息"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM employees WHERE employee_id = ?", (employee_id,))
        row = cursor.fetchone()
        if row:
            columns = [description[0] for description in cursor.description]
            return dict(zip(columns, row))
        return None


def search_employees(query: str, limit: int = 20) -> List[Dict[str, Any]]:
    """
    搜索员工，支持通过工号或姓名模糊搜索
    
    参数:
    - query: 搜索关键词
    - limit: 最大返回结果数量
    
    返回:
    - 匹配的员工列表
    """
    search_query = f"%{query}%"  # 添加通配符用于模糊搜索
    
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT * FROM employees 
            WHERE employee_id LIKE ? OR name LIKE ? 
            ORDER BY name 
            LIMIT ?
        """, (search_query, search_query, limit))
        
        columns = [description[0] for description in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]


def create_employee(employee_data: Dict[str, Any]) -> Dict[str, Any]:
    """创建新员工"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            INSERT INTO employees (
                employee_id, name, affiliation, dept1, dept2, dept3,
                position, hire_date, leave_date
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            employee_data["employee_id"],
            employee_data["name"],
            employee_data["affiliation"],
            employee_data["dept1"],
            employee_data["dept2"],
            employee_data["dept3"],
            employee_data["position"],
            employee_data["hire_date"],
            employee_data.get("leave_date")
        ))
        conn.commit()
        return get_employee(employee_data["employee_id"])


def update_employee(employee_id: str, employee_data: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    """更新员工信息"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            UPDATE employees SET
                name = ?, affiliation = ?, dept1 = ?, dept2 = ?, dept3 = ?,
                position = ?, hire_date = ?, leave_date = ?
            WHERE employee_id = ?
        """, (
            employee_data["name"],
            employee_data["affiliation"],
            employee_data["dept1"],
            employee_data["dept2"],
            employee_data["dept3"],
            employee_data["position"],
            employee_data["hire_date"],
            employee_data.get("leave_date"),
            employee_id
        ))
        conn.commit()
        return get_employee(employee_id)


def delete_employee(employee_id: str) -> bool:
    """删除员工"""
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM employees WHERE employee_id = ?", (employee_id,))
        conn.commit()
        return cursor.rowcount > 0


def batch_create_employees(employees: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    """批量创建员工"""
    created_employees = []
    with get_db() as conn:
        cursor = conn.cursor()
        try:
            # 开始事务
            cursor.execute("BEGIN TRANSACTION")

            # 准备批量插入的数据
            values = []
            employee_ids = []
            for employee in employees:
                values.append((
                    employee["employee_id"],
                    employee["name"],
                    employee["affiliation"],
                    employee["dept1"],
                    employee["dept2"],
                    employee["dept3"],
                    employee["position"],
                    employee["hire_date"],
                    employee.get("leave_date")
                ))
                employee_ids.append(employee["employee_id"])

            # 批量插入数据
            cursor.executemany("""
                INSERT INTO employees (
                    employee_id, name, affiliation, dept1, dept2, dept3,
                    position, hire_date, leave_date
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, values)

            # 获取新创建的记录
            placeholders = ','.join(['?'] * len(employee_ids))
            cursor.execute(f"""
                SELECT * FROM employees 
                WHERE employee_id IN ({placeholders})
            """, employee_ids)

            # 将结果转换为字典列表
            columns = [description[0] for description in cursor.description]
            created_employees = [dict(zip(columns, row)) for row in cursor.fetchall()]

            # 提交事务
            conn.commit()

        except sqlite3.IntegrityError as e:
            # 回滚事务
            conn.rollback()
            print(f"数据完整性错误: {str(e)}")
            raise e
        except Exception as e:
            # 回滚事务
            conn.rollback()
            print(f"意外错误: {str(e)}")
            raise e

    return created_employees
